package com.young.cms.web;

import com.young.cms.model.Column;
import com.young.cms.model.Table;
import com.young.cms.service.ICmsDBService;
import com.young.common.core.excel.ExcelExport;
import com.young.common.util.*;
import com.young.interfaces.log.annotation.Log;
import freemarker.template.Configuration;
import freemarker.template.Template;
import net.sf.json.JSONObject;
import org.apache.commons.io.IOUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;

import javax.annotation.Resource;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.util.*;

/**
 * 数据库操作
 * Created by Administrator on 2017/5/8.
 */
@Controller
@RequestMapping("/cms/db")
public class CmsDBOperController {

    private static final Logger logger = LoggerFactory.getLogger(CmsDBOperController.class);

    @Resource(name = "cmsDBService")
    ICmsDBService cmsDBService;


    /**
     * 获取所有的表信息
     * @param request
     * @return
     * @throws Exception
     */
    @Log("获取所有的表信息")
    @RequestMapping(value = "/getTableList", method = {RequestMethod.GET, RequestMethod.POST})
    @ResponseBody
    public Object getTableList(HttpServletRequest request) throws Exception{

        return Result.buildSuccess(cmsDBService.getTableListOfSimpleInfo());
    }

    /**
     * 查询某表的字段信息
     * @param tableName
     * @param request
     * @return
     * @throws Exception
     */
    @Log("查询某表的字段信息")
    @RequestMapping(value = "/getColumsByTableName", method = {RequestMethod.GET, RequestMethod.POST})
    @ResponseBody
    public Object getColumsByTableName(String tableName, HttpServletRequest request) throws Exception{

        return Result.buildSuccess(cmsDBService.getColumsByTableName(tableName));
    }

    /**
     * 下载数据库说明文档Excel
     * @param tableIds 待导出表名
     * @param all 是否导出全部(带平台表)
     * @param request
     * @param response
     * @throws Exception
     */
    @Log("下载数据库说明文档Excel")
    @RequestMapping(value = "/downloadDBExcel", method = {RequestMethod.GET, RequestMethod.POST})
    public void downloadDBExcel(String tableIds, boolean all, HttpServletRequest request, HttpServletResponse response) throws Exception{
        String[] tables = null;
        List<Table> tableList = cmsDBService.getTableListOfSimpleInfo();//库中所有的表
        List<Table> checkTableList = new LinkedList<Table>();//待导出表
        if(StringUtils.isNotBlank(tableIds)){//当存在已选的表时,从所有的表中将被选中的挑出来
            //将已选表转为map形式,通过hash的方式降低之后筛选的复杂度
            Map<String, String> map = new HashMap<String, String>();
            String[] arr = tableIds.split(",");
            for (String one : arr){
                map.put(one, "1");
            }

            //遍历库中的所有表,将被选中的挑出来
            for (Table table : tableList){
                if (map.containsKey(table.getId())){//存在该表,则放入checkTableList
                    checkTableList.add(table);
                }
            }

        }else{//未选择固定的表,表示导出所有
            checkTableList = tableList;
        }

        //遍历checkTableList,查询各个表的字段信息
        for (Table table : checkTableList){
            table.setColumnList(cmsDBService.getColumsByTableName(table.getId()));
        }

        if (!all){//排除平台表
            Iterator<Table> it = checkTableList.iterator();
            while(it.hasNext()){
                Table table = it.next();
                if (table.getId().toLowerCase().indexOf("tb_cms") == 0 || table.getId().toLowerCase().indexOf("tb_ums") == 0
                        || table.getId().toLowerCase().indexOf("tb_fs") == 0 || table.getId().toLowerCase().indexOf("tb_common") == 0
                        || table.getId().toLowerCase().indexOf("tb_platform") == 0){
                    it.remove();
                }
            }
        }

        //生成excel
        ExcelExport ee  = new ExcelExport();
        for (Table table : checkTableList){
            ee.insertRow(new String[]{table.getId()+"("+table.getName()+")"});
            ee.insertRow(new String[]{"字段", "类型", "长度", "非空", "注释"});
            for (Column col : table.getColumnList()){
                ee.insertRow(new Object[]{col.getField(), col.getProType(), col.getLength(), "1".equals(col.getNotNull()) ? "是" : "否", col.getRemark()});
            }
            ee.insertRow(new String[]{});//插入空行
        }
        ee.write2Response(response, "数据字典_"+ DateUtil.getCurrentDay()+".xls");
    }

    /**
     * 下载数据库说明文档word
     * @param tableIds 待导出的表名
     * @param all 是否全部导出(含平台表)
     * @param request
     * @param response
     * @throws Exception
     */
    @Log("下载数据库说明文档word")
    @RequestMapping(value = "/downloadDBDoc", method = {RequestMethod.GET, RequestMethod.POST})
    public void downloadDBDoc(String tableIds, boolean all, HttpServletRequest request, HttpServletResponse response) throws Exception{
        String[] tables = null;
        List<Table> tableList = cmsDBService.getTableListOfSimpleInfo();//库中所有的表
        List<Table> checkTableList = new LinkedList<Table>();//待导出表
        if(StringUtils.isNotBlank(tableIds)){//当存在已选的表时,从所有的表中将被选中的挑出来
            //将已选表转为map形式,通过hash的方式降低之后筛选的复杂度
            Map<String, String> map = new HashMap<String, String>();
            String[] arr = tableIds.split(",");
            for (String one : arr){
                map.put(one, "1");
            }

            //遍历库中的所有表,将被选中的挑出来
            for (Table table : tableList){
                if (map.containsKey(table.getId())){//存在该表,则放入checkTableList
                    checkTableList.add(table);
                }
            }

        }else{//未选择固定的表,表示导出所有
            checkTableList = tableList;
        }

        //遍历checkTableList,查询各个表的字段信息
        for (Table table : checkTableList){
            table.setColumnList(cmsDBService.getColumsByTableName(table.getId()));
        }

        if (!all){//排除平台表
            Iterator<Table> it = checkTableList.iterator();
            while(it.hasNext()){
                Table table = it.next();
                if (table.getId().toLowerCase().indexOf("tb_cms") == 0 || table.getId().toLowerCase().indexOf("tb_ums") == 0
                        || table.getId().toLowerCase().indexOf("tb_fs") == 0 || table.getId().toLowerCase().indexOf("tb_common") == 0
                        || table.getId().toLowerCase().indexOf("tb_platform") == 0){
                    it.remove();
                }
            }
        }

        //格式化
        for (Table table : checkTableList){
            for (Column col : table.getColumnList()){
                col.setNotNull("1".equals(col.getNotNull()) ? "是" : "否");
            }
        }

        //配置
        Configuration configuration = new Configuration();
        configuration.setClassForTemplateLoading(this.getClass(), "/template/db/");//注意路径以斜杠'/'开头

        //获取模板文件
        Template template = configuration.getTemplate("db_doc.ftl", "UTF-8");//, "UTF-8"

        Map<String, Object> map = new HashMap<String, Object>();
        map.put("tableList", checkTableList);

        response.setContentType("application/x-msdownload;charset=UTF-8");//文件下载类型
        FileDownloadUtil.setFileNameOfCN(request, response, "数据库文档_"+ DateUtil.getCurrentDay()+".doc");
        OutputStream os = response.getOutputStream();
        try {
            Writer fw = new BufferedWriter(new OutputStreamWriter(response.getOutputStream(), "UTF-8"));
            template.process(map, fw);
            fw.flush();
            fw.close();
        } finally {
            os.flush();
            os.close();
        }
    }

    /**
     * 备份表结构
     * @param tableIds
     * @param request
     * @param response
     * @throws Exception
     */
    @Log("备份表结构")
    @RequestMapping(value = "/backupTable", method = {RequestMethod.GET, RequestMethod.POST})
    public void backupTable(String tableIds, HttpServletRequest request, HttpServletResponse response) throws Exception{
        //参数校验
        if (StringUtils.isBlank(tableIds)){
            response.getWriter().write("表名无效!");
        }
        String[] tableArr = tableIds.split(",");
        if (tableArr == null || tableArr.length == 0){
            response.getWriter().write("表名无效!");
        }
        Set<String> tableSet = new HashSet<String>(Arrays.asList(tableArr));//便于后面的查询

        //库中所有的表
        List<Table> tableList = cmsDBService.getTableListOfSimpleInfo();
        if (tableList == null || tableList.size() == 0){
            response.getWriter().write("数据库中没有表!");
        }
        //筛选中选中的表信息
        Iterator<Table> iterator = tableList.iterator();
        while (iterator.hasNext()){
            Table table = iterator.next();
            if (!tableSet.contains(table.getId())){//表不在入参里面,就移除
                iterator.remove();
            }
        }

        //循环查询各表的字段信息
        for (Table table : tableList){
            //查询表的字段信息
            table.setColumnList(cmsDBService.getColumsByTableName(table.getId()));
        }

        //解析为json字符串
        String str = CommonJsonUtil.beanListToJsonStrArr(tableList);
        logger.info("[备份表结构] 备份内容:{}", str);
        //将json串写入流中供下载
        byte[] bytes = str.getBytes("UTF-8");
        //下载处理
        response.setContentType("application/x-msdownload;");//文件下载类型
        FileDownloadUtil.setFileNameOfCN(request, response, "backup" + DateUtil.dateFormat(new Date(), "yyyyMMddHHmmss") + ".table");//设置中文文件名
        OutputStream os = response.getOutputStream();
        os.write(bytes);
        os.close();
    }

    /**
     * 恢复表结构
     * @param file
     * @param request
     * @param response
     * @return
     * @throws Exception
     */
    @Log("恢复表结构")
    @RequestMapping(value = "/recoverTable", method = {RequestMethod.GET, RequestMethod.POST})
    @ResponseBody
    public Object recoverTable(MultipartFile file, HttpServletRequest request, HttpServletResponse response) throws Exception {
        if (file != null && !file.isEmpty()){
            InputStream is = file.getInputStream();
            ByteArrayOutputStream byteArrayOutputStream = new ByteArrayOutputStream();
            IOUtils.copy(is, byteArrayOutputStream);
            byte[] bytes = byteArrayOutputStream.toByteArray();
            String str = new String(bytes, "UTF-8");
            logger.info("[备份表结构] 恢复内容:{}", str);
            List<JSONObject> list = CommonJsonUtil.jsonStrArrToBeanList(str, JSONObject.class);
            if (list != null && list.size() > 0){
                Map<String, Class> map = new HashMap<String, Class>();
                map.put("columnList", Column.class);
                for (JSONObject jsonObject : list){
                    //转为Table对象
                    Table table = (Table)JSONObject.toBean(jsonObject, Table.class, map);
                    cmsDBService.createTable(table);//创建表
                }
            }
        }
        return Result.buildSuccess();
    }
}
